package vip.xiaonuo.smzq.modular.nbfb.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.kcl.entity.Zycl;
import vip.xiaonuo.smzq.modular.nbfb.entity.vo.NbfbThreeVo;

import java.util.List;

@Mapper
public interface ZyclThreeMapper extends BaseMapper<Zycl> {
    @Select("WITH base AS (\n" +
            "\tSELECT\n" +
            "\t\tzh.mc,\n" +
            "\t\tzh.dm,\n" +
            "\t\tks.kcmc,\n" +
            "\tCASE\n" +
            "\t\t\t\n" +
            "\t\t\tWHEN ks.kcdm IN ( 42201, 42202, 32006, 63701 ) THEN\n" +
            "\t\t\t'' ELSE ks.jsdxmc \n" +
            "\t\tEND jsdxmc,\n" +
            "\tks.kqbh,\n" +
            "\tks.kcdm,\n" +
            "CASE\n" +
            "\t\n" +
            "\tWHEN ks.kcdm IN ( 42201, 42202, 32006, 63701 ) THEN\n" +
            "\t0 ELSE ks.tjdx \n" +
            "\tEND tjdx,\n" +
            "\tMAX ( CASE WHEN kc.xsbs = 0 THEN kc.jsldw ELSE kc.ksldw END ) dw,\n" +
            "\tSUM ( CASE WHEN kc.xsbs = 0 THEN zy.ncbyjsl ELSE zy.ncbyksl END ) ncby,\n" +
            "\tMAX ( sfly ) sfly,\n" +
            "\tSUM ( CASE WHEN kc.xsbs = 0 THEN zy.nmbyjsl ELSE zy.nmbyksl END ) nmby \n" +
            "FROM\n" +
            "\thisi_kshz ks\n" +
            "\tLEFT JOIN hisi_ks_zyclhz zy ON ks.nd = zy.nd \n" +
            "\tAND ks.kqbh = zy.kqbh \n" +
            "\tAND ks.djflbh = zy.djflbh \n" +
            "\tAND ks.kcdm = zy.kcdm \n" +
            "\tAND ks.tjdx = zy.tjdx\n" +
            "\tLEFT JOIN kcl_cb06_kc kc ON ks.kcdm = kc.kcdm \n" +
            "\tAND ks.tjdx = kc.tjdx\n" +
            "\tLEFT JOIN kcl_cb01_zh zh ON kc.kclb :: int8 / 10 * 100 = zh.dm :: int8 \n" +
            "WHERE\n" +
            "\tks.nd = #{nd} \n" +
            "\tAND ks.sbbsm <> 3 \n" +
            "\tAND zy.zycllx IN ( 'TM', 'KZ', 'TD' ) \n" +
            "GROUP BY\n" +
            "\tks.kcmc,\n" +
            "\tzh.dm,\n" +
            "\tks.jsdxmc,\n" +
            "\tks.kcdm,\n" +
            "\tks.tjdx,\n" +
            "\tzh.mc,\n" +
            "\tks.kqbh \n" +
            "\t),\n" +
            "-- 原始数据\n" +
            "\tDATA AS (\n" +
            "\tSELECT\n" +
            "\tCASE\n" +
            "\t\t\t\n" +
            "\t\tWHEN\n" +
            "\t\t\tmc IS NULL \n" +
            "\t\t\tOR mc = '' THEN\n" +
            "\t\t\t\t'贵金属、稀有分散元素矿产' ELSE mc \n" +
            "\t\t\tEND AS mc,\n" +
            "\t\tCASE\n" +
            "\t\t\t\t\n" +
            "\t\t\t\tWHEN mc IS NULL \n" +
            "\t\t\t\tOR mc = '' THEN\n" +
            "\t\t\t\t\t'700400' ELSE dm \n" +
            "\t\t\t\t\tEND AS dm,\n" +
            "\t\t\t\tkcdm,\n" +
            "\t\t\t\t( CASE WHEN jsdxmc <> '' THEN CONCAT ( kcmc, ' (', jsdxmc, ')' ) ELSE kcmc END ) AS kcmc,\n" +
            "\t\t\t\tMAX ( dw ) cldw,\n" +
            "\t\t\t\tCAST ( SUM ( nmby ) AS DECIMAL ( 18, 2 ) ) nmbyzyl,\n" +
            "\t\t\t\tCOUNT ( 1 ) kcdzs,\n" +
            "\t\t\t\tSUM ( sfly ) ylykcds,\n" +
            "\t\t\t\tCAST ( SUM ( CASE WHEN sfly = 1 THEN nmby ELSE 0 END ) AS DECIMAL ( 18, 2 ) ) ylyzyl,\n" +
            "\t\t\t\tCAST (\n" +
            "\t\t\t\tSUM ( CASE WHEN sfly = 1 THEN nmby ELSE 0 END ) / COALESCE ( NULLIF ( SUM ( nmby ), 0 ), 1 ) * 100 AS DECIMAL ( 18, 2 ) \n" +
            "\t\t\t) ylybfb,\n" +
            "\t\t\tCOUNT ( 1 ) - SUM ( sfly ) wlykcds,\n" +
            "\t\t\tCAST ( SUM ( CASE WHEN sfly = 0 THEN nmby ELSE 0 END ) AS DECIMAL ( 18, 2 ) ) wlyzyl,\n" +
            "\t\t\tCAST (\n" +
            "\t\t\tSUM ( CASE WHEN sfly = 0 THEN nmby ELSE 0 END ) / COALESCE ( NULLIF ( SUM ( nmby ), 0 ), 1 ) * 100 AS DECIMAL ( 18, 2 ) \n" +
            "\t\t) wlybfb \n" +
            "\tFROM\n" +
            "\t\tbase \n" +
            "\tGROUP BY\n" +
            "\t\tmc,\n" +
            "\t\tdm,\n" +
            "\t\tkcmc,\n" +
            "\t\tjsdxmc,\n" +
            "\t\tkcdm,\n" +
            "\t\ttjdx \n" +
            "\t),\n" +
            "-- 小计\n" +
            "\txj AS (\n" +
            "\tSELECT\n" +
            "\t\tmc,\n" +
            "\t\tdm,\n" +
            "\t\t99999 AS kcdm,\n" +
            "\t\t'小计' AS kcmc,\n" +
            "\t\t'' AS cldw,\n" +
            "\t\t0 AS nmbyzyl,\n" +
            "\t\tSUM ( kcdzs ) AS kcdzs,\n" +
            "\t\tSUM ( ylykcds ) AS ylykcds,\n" +
            "\t\t0 AS ylyzyl,\n" +
            "\t\t0 AS ylybfb,\n" +
            "\t\tSUM ( wlykcds ) AS wlykcds,\n" +
            "\t\t0 AS wlyzyl,\n" +
            "\t\t0 AS wlybfb \n" +
            "\tFROM\n" +
            "\tDATA \n" +
            "\tGROUP BY\n" +
            "\t\tmc,\n" +
            "\t\tdm \n" +
            "\t),\n" +
            "-- 总计\n" +
            "\tzj AS (\n" +
            "\tSELECT\n" +
            "\t\t'' AS mc,\n" +
            "\t\t9999999 AS dm,\n" +
            "\t\t199999 AS kcdm,\n" +
            "\t\t'总计' AS kcmc,\n" +
            "\t\t'' AS cldw,\n" +
            "\t\t0 AS nmbyzyl,\n" +
            "\t\tSUM ( kcdzs ) AS kcdzs,\n" +
            "\t\tSUM ( ylykcds ) AS ylykcds,\n" +
            "\t\t0 AS ylyzyl,\n" +
            "\t\t0 AS ylybfb,\n" +
            "\t\tSUM ( wlykcds ) AS wlykcds,\n" +
            "\t\t0 AS wlyzyl,\n" +
            "\t\t0 AS wlybfb \n" +
            "\tFROM\n" +
            "\tDATA \n" +
            "\t) SELECT\n" +
            "\tmc AS kl,\n" +
            "\tkcmc,\n" +
            "\tcldw,\n" +
            "\tnmbyzyl,\n" +
            "\tkcdzs,\n" +
            "\tylykcds,\n" +
            "\tylyzyl,\n" +
            "\tylybfb,\n" +
            "\twlykcds,\n" +
            "\twlyzyl,\n" +
            "\twlybfb \n" +
            "FROM\n" +
            "\t( SELECT * FROM DATA UNION ALL SELECT * FROM xj UNION ALL SELECT * FROM zj ) T \n" +
            "ORDER BY\n" +
            "\tdm,\n" +
            "\tkcdm,\n" +
            "\tkcmc;")
    List<NbfbThreeVo> getNbfbThree(Integer nd);
}
